using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using PetShop.BusinessEntities;
using PetShop.DataAccess.Utilities;

namespace PetShop.DataAccess.DAO
{
    public class ProductDAO
    {
        private const string SQLSelectProducts = "SELECT ProductId, Name, Descn FROM Product";

        private const string SQLSelectProductsByCategory =
            "SELECT ProductId, Name FROM Product WHERE Category = @Category";

        private const string SQLSelectProductsBySearch1 = "SELECT ProductId, Name, Descn FROM Product WHERE ((";

        private const string SQLSelectProductsBySearch2 =
            "LOWER(Name) LIKE '%' + {0} + '%' OR LOWER(Category) LIKE '%' + {0} + '%'";

        private const string SQLSelectProductsBySearch3 = ") OR (";
        private const string SQLSelectProductsBySearch4 = "))";
        private const string ParmCategory = "@Category";
        private const string ParmKeyword = "@Keyword";


        public List<ProductInfo> GetProductsByCategory(string category)
        {
            var productsByCategory = new List<ProductInfo>();

            var parm = new SqlParameter(ParmCategory, SqlDbType.Char, 10);
            parm.Value = category;


            using (
                SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.ConnString, CommandType.Text,
                                                            SQLSelectProductsByCategory, parm))
            {
                while (rdr.Read())
                {
                    var product = new ProductInfo(rdr.GetString(0), rdr.GetString(1), null);
                    productsByCategory.Add(product);
                }
            }

            return productsByCategory;
        }


        public List<ProductInfo> GetProductsBySearch(string[] keywords)
        {
            var productsBySearch = new List<ProductInfo>();

            int numKeywords = keywords.Length;


            var sql = new StringBuilder(SQLSelectProductsBySearch1);


            for (int i = 0; i < numKeywords; i++)
            {
                sql.Append(string.Format(SQLSelectProductsBySearch2, ParmKeyword + i));
                sql.Append(i + 1 < numKeywords ? SQLSelectProductsBySearch3 : SQLSelectProductsBySearch4);
            }

            string sqlProductsBySearch = sql.ToString();
            SqlParameter[] parms = SQLHelper.GetCachedParameters(sqlProductsBySearch);


            if (parms == null)
            {
                parms = new SqlParameter[numKeywords];

                for (int i = 0; i < numKeywords; i++)
                    parms[i] = new SqlParameter(ParmKeyword + i, SqlDbType.VarChar, 80);

                SQLHelper.CacheParameters(sqlProductsBySearch, parms);
            }


            for (int i = 0; i < numKeywords; i++)
                parms[i].Value = keywords[i];


            using (
                SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.ConnString, CommandType.Text, sqlProductsBySearch,
                                                            parms))
            {
                while (rdr.Read())
                {
                    var product = new ProductInfo(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2));
                    productsBySearch.Add(product);
                }
            }

            return productsBySearch;
        }
    }
}